﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DVSProject.Models;

namespace DVSProject.Lib
{
    public class MerchantRepo
    {
        public const string m_msg_head = "Merchant";

        public IEnumerable<MerchantModels> GetMerchant()
        {
            List<MerchantModels> Merchants = new List<MerchantModels>();
            string SQLCommand = @"select * from TBMerchant WHERE Status <> 'Delete'";
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        MerchantModels Merchant = new MerchantModels();
                        Merchant.MerchantId = (int)reader["MerchantId"];
                        Merchant.mcode = (string)reader["mcode"];
                        Merchant.NameTh = (string)reader["NameTh"];
                        Merchant.NameEng = (string)reader["NameEng"];
                        Merchant.AddressTh = (string)reader["AddressTh"];
                        Merchant.AddressEng = (string)reader["AddressEng"];
                        Merchant.Tel =  (reader["Tel"] !=DBNull.Value) ? (string)reader["Tel"]:"";
                        Merchant.Fax = (reader["Fax"] !=DBNull.Value) ? (string)reader["Fax"]:"";
                        Merchant.Status = (string)reader["Status"];
                        Merchant.CreateBy = (string)reader["CreateBy"];
                        Merchant.CreateOn = (DateTime)reader["CreateOn"];
                        Merchant.UpdateBy = (string)reader["UpdateBy"];
                        Merchant.UpdateOn = (DateTime)reader["UpdateOn"];
                        Merchants.Add(Merchant);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return Merchants.ToList();
        }

        public MerchantModels GetMerchantRemarkById(int MerchantId)
        {
            MerchantModels Merchant = new MerchantModels();
            string SQLCommand = @"select * from TBMerchant where MerchantId=@MerchantId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@MerchantId", MerchantId);
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        Merchant.MerchantId = (int)reader["MerchantId"];
                        Merchant.mcode = (string)reader["mcode"];
                        Merchant.NameTh = (string)reader["NameTh"];
                        Merchant.NameEng = (string)reader["NameEng"];
                        Merchant.AddressTh = (string)reader["AddressTh"];
                        Merchant.AddressEng = (string)reader["AddressEng"];
                        Merchant.Tel = (reader["Tel"] !=DBNull.Value) ? (string)reader["Tel"]:"";
                        Merchant.Fax = (reader["Fax"] !=DBNull.Value) ?(string)reader["Fax"]:"";
                        Merchant.Status = (string)reader["Status"];
                        Merchant.CreateBy = (string)reader["CreateBy"];
                        Merchant.CreateOn = (DateTime)reader["CreateOn"];
                        Merchant.UpdateBy = (string)reader["UpdateBy"];
                        Merchant.UpdateOn = (DateTime)reader["UpdateOn"];
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                }
            }
            return Merchant;
        }

        public bool Create(MerchantModels Merchant)
        {
            bool result = true;
            string SQLCommand = @"INSERT into TBMerchant
                                    values (@mcode,@NameTh,@NameEng,@AddressTh,@AddressEng,@Tel,@Fax,@Status,@UserId,
                                    GETDATE(),@UserId,GETDATE())";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@mcode", Merchant.mcode);
                    db.AddParameter("@NameTh", Merchant.NameTh);
                    db.AddParameter("@NameEng", Merchant.NameEng);
                    db.AddParameter("@AddressTh", Merchant.AddressTh);
                    db.AddParameter("@AddressEng", Merchant.AddressEng);
                    db.AddParameter("@Tel", Merchant.Tel);
                    db.AddParameter("@Fax", Merchant.Fax);
                    db.AddParameter("@Status", "Active");
                    db.AddParameter("@UserID", Merchant.CreateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Edit(MerchantModels Merchant)
        {
            bool result = true;
            string SQLCommand = @"Update TBMerchant 
                                    Set mcode=@mcode, NameTh=@NameTh,NameEng=@NameEng,AddressTh=@AddressTh,
                                        AddressEng=@AddressEng,Tel=@Tel, Fax=@Fax,UpdateBy=@UserId,UpdateOn=getdate()
                                    where MerchantId=@MerchantId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@MerchantId", Merchant.MerchantId);
                    db.AddParameter("@mcode", Merchant.mcode);
                    db.AddParameter("@NameTh", Merchant.NameTh);
                    db.AddParameter("@NameEng", Merchant.NameEng);
                    db.AddParameter("@AddressTh", Merchant.AddressTh);
                    db.AddParameter("@AddressEng", Merchant.AddressEng);
                    db.AddParameter("@Tel", Merchant.Tel);
                    db.AddParameter("@Fax", Merchant.Fax);
                    db.AddParameter("@UserID", Merchant.UpdateBy );

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Delete(MerchantModels Merchant)
        {
            bool result = true;
            string SQLCommand = @"Update TBMerchant
                                    Set Status=@Status,UpdateBy=@UserId,UpdateOn=getdate()
                                    where MerchantId=@MerchantId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@MerchantId", Merchant.MerchantId);
                    db.AddParameter("@Status", "Delete");
                    db.AddParameter("@UserID", Merchant.UpdateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0005", ex));
                    return false;
                }
            }
            return result;
        }
    }
}